﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;

/// <summary>
/// Summary description for User_Session
/// </summary>
public class User_Session
{
    private static SqlConnection sqlConnection;

    private DataTable objDataTable;
    public static SqlDataAdapter sqlAdapter;
    public static SqlCommand sqlCommand; 
	public User_Session()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    
    public static User Get_User_ByID(System.Int32 User_ID)
    {
        
        User obj = new User();
        try
        {
            DataTable dt = new DataTable(); // chu y cho nay nek
            dt = DataProvider.GetDataTable("select * from tblUser where User_ID = " + User_ID);

            obj.User_ID = (dt.Rows[0]["User_ID"] is DBNull) ? int.MinValue : (System.Int32)dt.Rows[0]["User_ID"];
            obj.User_Name = (dt.Rows[0]["User_Name"] is DBNull) ? string.Empty : (System.String)dt.Rows[0]["User_Name"];
            obj.User_Email = (dt.Rows[0]["User_Email"] is DBNull) ? string.Empty : (System.String)dt.Rows[0]["User_Email"];
            obj.User_Address = (dt.Rows[0]["User_Address"] is DBNull) ? string.Empty : (System.String)dt.Rows[0]["User_Address"];
            obj.User_Password = (dt.Rows[0]["User_Password"] is DBNull) ? string.Empty : (System.String)dt.Rows[0]["User_Password"];
            obj.User_Sex = (dt.Rows[0]["User_Sex"] is DBNull) ? false : (System.Boolean)dt.Rows[0]["User_Sex"];
            obj.User_Phone = (dt.Rows[0]["User_Phone"] is DBNull) ? string.Empty : (System.String)dt.Rows[0]["User_Phone"];
            obj.User_Company = (dt.Rows[0]["User_Company"] is DBNull) ? string.Empty : (System.String)dt.Rows[0]["User_Company"];
            obj.User_Status = (dt.Rows[0]["User_Status"] is DBNull) ? false : (System.Boolean)dt.Rows[0]["User_Status"];
            obj.User_Type = (dt.Rows[0]["User_Type"] is DBNull) ? int.MinValue : (System.Int32)dt.Rows[0]["User_Type"];
            obj.User_IsAdmin = (dt.Rows[0]["User_IsAdmin"] is DBNull) ? false : (System.Boolean)dt.Rows[0]["User_IsAdmin"];
            obj.User_Nick = (dt.Rows[0]["User_Nick"] is DBNull) ? string.Empty : (System.String)dt.Rows[0]["User_Nick"];
        }
        catch (Exception e) { WebMsgBox.Show("ngoai le");  }
        return obj;
    }

    public static bool IsExitUser(string userName, string password, ref string mem_ID)
    {
        DataTable dt = new DataTable(); // chu y cho nay nek
        dt = DataProvider.GetDataTable("select * from tblUser where User_Name = '"+ userName.Trim()+"' and User_Password ='"+ password.Trim()+"'");
       // WebMsgBox.Show(dt.Rows.Count.ToString());
        if (dt == null || dt.Rows.Count == 0)
            return false;
        else
        {
            mem_ID = (dt.Rows[0]["User_ID"].ToString());
            return true;
        }
    }

    public static bool IsExitUser(string email)
    {
        DataTable dt = new DataTable(); // chu y cho nay nek
        dt = DataProvider.GetDataTable("select * from tblUser where User_Email = '" + email.Trim() + "'");
        if (dt == null || dt.Rows.Count == 0)
            return false;
        else
            return true;
    }

    public static bool Insert_User(User user)
    {
        /*
        try
        {*/
            sqlConnection = DataProvider.CreateConnection();
            
            //===========================kiem tra coi da ton tai email nay chua-=============================//
            if (!IsExitUser(user.User_Email.Trim()))// da ton tai user roi
                return false;
            //================================== Thỏa mãn các điều kiện và thêm vào ============================//
            DataTable datatable = new DataTable();
            sqlAdapter = new SqlDataAdapter("select * from tblUser", sqlConnection);
            sqlAdapter.Fill(datatable);

            DataRow datarow = datatable.NewRow();
            datarow[1] = user.User_Name;
            datarow[2] = user.User_Email;
            datarow[3] = user.User_Address;
            datarow[4] = user.User_Password;
            datarow[5] = user.User_Sex;
            datarow[6] = user.User_Phone;
            datarow[7] = user.User_Company;
            datarow[8] = user.User_Status;
            datarow[9] = user.User_Type;
            datarow[10] = user.User_IsAdmin;
            datarow[11] = user.User_Nick;


            datatable.Rows.Add(datarow);

            sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = "INSERT INTO tblUser VALUES(@User_Name,@User_Email,@User_Address,@User_Password,@User_Sex,@User_Phone,@User_Company,@User_Status,@User_Type,@User_IsAdmin,@User_Nick)";
            sqlCommand.Parameters.Add("@User_Name", SqlDbType.VarChar, 50, "User_Name");
            sqlCommand.Parameters.Add("@User_Email", SqlDbType.VarChar, 50, "User_Email");
            sqlCommand.Parameters.Add("@User_Address", SqlDbType.NVarChar, 150, "User_Address");
            sqlCommand.Parameters.Add("@User_Password", SqlDbType.NVarChar, 150, "User_Password");
            sqlCommand.Parameters.Add("@User_Sex", SqlDbType.Bit, 1, "User_Sex");
            sqlCommand.Parameters.Add("@User_Phone", SqlDbType.NVarChar, 50, "User_Phone");
            sqlCommand.Parameters.Add("@User_Company", SqlDbType.NVarChar, 50, "User_Company");
            sqlCommand.Parameters.Add("@User_Status", SqlDbType.Bit, 1, "User_Status");
            sqlCommand.Parameters.Add("@User_Type", SqlDbType.Int, 10, "User_Type");
            sqlCommand.Parameters.Add("@User_IsAdmin", SqlDbType.Bit, 1, "User_IsAdmin");
            sqlCommand.Parameters.Add("@User_Nick", SqlDbType.NVarChar, 50, "User_Nick");

            sqlAdapter.InsertCommand = sqlCommand;
            sqlAdapter.Update(datatable);
            sqlCommand.Dispose();
            sqlCommand = null;
            return true;
            /*
        }
        catch (Exception e)
        {
            WebMsgBox.Show("ngoai le");
            return false;
        }*/
    }
    public static List<User> GetList_User()
    {
        DataTable dt = DataProvider.GetDataTable("select * from tblUser");
        List<User> lst = new List<User>();

        int  i =0;
        for (i = 0; i < dt.Rows.Count; i++)
        {
            User obj = new User();

            obj.User_ID = (dt.Rows[i]["User_ID"] is DBNull) ? int.MinValue : (System.Int32)dt.Rows[i]["User_ID"];
            obj.User_Name = (dt.Rows[i]["User_Name"] is DBNull) ? string.Empty : (System.String)dt.Rows[i]["User_Name"];
            obj.User_Email = (dt.Rows[i]["User_Email"] is DBNull) ? string.Empty : (System.String)dt.Rows[i]["User_Email"];
            obj.User_Address = (dt.Rows[i]["User_Address"] is DBNull) ? string.Empty : (System.String)dt.Rows[i]["User_Address"];
            obj.User_Password = (dt.Rows[i]["User_Password"] is DBNull) ? string.Empty : (System.String)dt.Rows[i]["User_Password"];
            obj.User_Sex = (dt.Rows[i]["User_Sex"] is DBNull) ? false : (System.Boolean)dt.Rows[i]["User_Sex"];
            obj.User_Phone = (dt.Rows[i]["User_Phone"] is DBNull) ? string.Empty : (System.String)dt.Rows[i]["User_Phone"];
            obj.User_Company = (dt.Rows[i]["User_Company"] is DBNull) ? string.Empty : (System.String)dt.Rows[i]["User_Company"];
            obj.User_Status = (dt.Rows[i]["User_Status"] is DBNull) ? false : (System.Boolean)dt.Rows[i]["User_Status"];
            obj.User_Type = (dt.Rows[i]["User_Type"] is DBNull) ? int.MinValue : (System.Int32)dt.Rows[i]["User_Type"];
            obj.User_IsAdmin = (dt.Rows[i]["User_IsAdmin"] is DBNull) ? false : (System.Boolean)dt.Rows[i]["User_IsAdmin"];
            obj.User_Nick = (dt.Rows[i]["User_Nick"] is DBNull) ? string.Empty : (System.String)dt.Rows[i]["User_Nick"];

            lst.Add(obj);
        }

       // WebMsgBox.Show(i.ToString());
        return lst;
    }
    public static void Delete_User(User temp)
    {
        String strSQL;
        // xoa inv
        strSQL = "delete  from Invetory where User_ID = " + temp.User_ID;
        sqlConnection = new SqlConnection();
        sqlConnection = DataProvider.CreateConnection();
        SqlCommand cmd = new SqlCommand(strSQL, sqlConnection);
        cmd.CommandType = CommandType.Text; ;
        cmd.ExecuteNonQuery();

        // xoa cm 
        strSQL = "delete  from Comments where User_ID = " + temp.User_ID;
        cmd = new SqlCommand(strSQL, sqlConnection);
        cmd.CommandType = CommandType.Text; ;
        cmd.ExecuteNonQuery();

        // xoa User co Pro lien quan
        strSQL = "delete  from tblUser where User_ID = " + temp.User_ID;
        cmd = new SqlCommand(strSQL, sqlConnection);
        cmd.CommandType = CommandType.Text; ;
        cmd.ExecuteNonQuery();

       

        sqlConnection.Close();
        cmd = null;
    }
    public static void Update_User(User temp)
    {
        

        sqlConnection = new SqlConnection();
        sqlConnection = DataProvider.CreateConnection();


        String strSQL;

        strSQL = "UPDATE tblUser  SET  User_Name = @User_Name , User_Email = @User_Email,User_Address = @User_Address, User_Password= @User_Password, User_Sex = @User_Sex, User_Phone = @User_Phone,User_Company = @User_Company , User_Type = @User_Type , User_Nick = @User_Nick  WHERE User_ID= @User_ID ";
        SqlCommand sqlCommand = new SqlCommand(strSQL, sqlConnection);
        sqlCommand.CommandType = CommandType.Text;

        //WebMsgBox.Show(temp.User_Name);

        sqlCommand.CommandText = strSQL;
        sqlCommand.Parameters.Add("@User_ID", SqlDbType.Int).Value = temp.User_ID;
        sqlCommand.Parameters.Add("@User_Name", SqlDbType.VarChar).Value = temp.User_Name;
        sqlCommand.Parameters.Add("@User_Email", SqlDbType.VarChar).Value = temp.User_Email;
        sqlCommand.Parameters.Add("@User_Address", SqlDbType.NVarChar).Value = temp.User_Address;
        sqlCommand.Parameters.Add("@User_Password", SqlDbType.NVarChar).Value = temp.User_Password;
        sqlCommand.Parameters.Add("@User_Sex", SqlDbType.Bit).Value = temp.User_Sex;
        sqlCommand.Parameters.Add("@User_Phone", SqlDbType.NVarChar).Value = temp.User_Phone;
        sqlCommand.Parameters.Add("@User_Company", SqlDbType.NVarChar).Value = temp.User_Company;
        sqlCommand.Parameters.Add("@User_Type", SqlDbType.Int).Value = temp.User_Type;
        sqlCommand.Parameters.Add("@User_Nick", SqlDbType.NVarChar).Value = temp.User_Nick;

        sqlCommand.ExecuteNonQuery();// tai sao bi bao loi
        sqlCommand.Dispose();
        sqlCommand = null;
    }

}
